﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_BGM_ProjectOperationExpense : IDAL_BGM_ProjectOperationExpense
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public IList<M_BGM_ProjectOperationExpense> GetFull(Nullable<int> bgm_proj_oper_seq
            , Nullable<int> bgm_source_seq, Nullable<int> bgm_exp_grp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationExpense> infoList = new List<M_BGM_ProjectOperationExpense>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                strSQL = "SELECT pjexp.bgm_proj_oper_exp_seq, pjexp.bgm_proj_oper_seq"
                    + ", pjexp.bgm_proj_oper_exp_descr, pjexp.bgm_proj_exp_seq"
                    + ", pjexp.amount"
                    + ", recexp.bgm_source_seq, source.bgm_source_name"
                    + ", pjexp.unit_price, pjexp.unit_qty"

                    + ", recexp.bgm_exp_seq, exp.bgm_exp_name"
                    + ", exp.bgm_exp_grp_seq, expgrp.bgm_exp_grp_name"
                    + ", pjexp.record_status"
                    + " FROM bgm_project_operation_expense pjexp"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_source source ON recexp.bgm_source_seq = source.bgm_source_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense_group expgrp ON exp.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
                    + " WHERE pjexp.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " AND pjexp.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + (bgm_source_seq != null ? " AND recexp.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_exp_grp_seq != null ? " AND exp.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationExpense info = new M_BGM_ProjectOperationExpense();
                        info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_exp_seq"]);
                        info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);
                        info.bgm_proj_oper_exp_descr = drc[i]["bgm_proj_oper_exp_descr"].ToString();
                        info.bgm_proj_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_exp_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        //info.currency_seq = cn.ConvertNullInt(drc[i]["currency_seq"]);
                        //info.exchange_rate = cn.ConvertNullDouble(drc[i]["exchange_rate"]);
                        //info.bath_amount = cn.ConvertNullDouble(drc[i]["bath_amount"]);
                        info.unit_price = cn.ConvertNullDouble(drc[i]["unit_price"]);
                        info.unit_qty = cn.ConvertNullInt(drc[i]["unit_qty"]);

                        M_BGM_ProjectExpense subInfo1 = new M_BGM_ProjectExpense();
                        M_BGM_MAS_Expense subInfo2 = new M_BGM_MAS_Expense();
                        M_BGM_MAS_Source subInfo4 = new M_BGM_MAS_Source();
                        //M_FIM_MAS_Currencies subInfo5 = new M_FIM_MAS_Currencies();

                        subInfo1.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);
                        subInfo1.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        subInfo2.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
                        subInfo2.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        subInfo2.bgm_exp_grp_name = drc[i]["bgm_exp_grp_name"].ToString();
                        subInfo4.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        //subInfo5.currency_name_short = drc[i]["currency_name_short"].ToString();

                        subInfo1.m_bgm_mas_expense = subInfo2;
                        subInfo1.m_bgm_mas_source = subInfo4;
                        info.m_bgm_projectexpense = subInfo1;
                        //info.m_fim_mas_currencies = subInfo5;

                        info.record_status = drc[i]["record_status"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        //public IList<M_BGM_ProjectOperationExpense> GetProjectExpense(Nullable<int> bgm_proj_oper_seq
        //    , Nullable<int> bgm_proj_seq)
        //{
        //    string strSQL;
        //    //string strWhere = "";
        //    DataRowCollection drc = null;
        //    IList<M_BGM_ProjectOperationExpense> infoList = new List<M_BGM_ProjectOperationExpense>();
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(3);

        //        dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
        //        dbManager.AddParameters(2, "@bgm_proj_seq", bgm_proj_seq);

        //        strSQL = "SELECT distinct recexp.bgm_source_seq, exp.bgm_exp_grp_seq"
        //            + " FROM bgm_project_expense recexp"
        //            + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
        //            + " WHERE recexp.bgm_proj_seq = @bgm_proj_seq";
        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

        //        if (drc.Count > 0)
        //        {
        //            for (int i = 0; i < drc.Count; i++)
        //            {
        //                string strBgmSourceSeq = drc[i]["bgm_source_seq"].ToString();
        //                string strBgmExpGrpSeq = drc[i]["bgm_exp_grp_seq"].ToString();

        //                if (i == 0)
        //                {
        //                    strWhere += " AND (";
        //                }

        //                strWhere += "(es.bgm_source_seq = " + strBgmSourceSeq + " and exp.bgm_exp_grp_seq = " + strBgmExpGrpSeq + ")";

        //                if (i == drc.Count - 1)
        //                {
        //                    strWhere += ")";
        //                }
        //                else
        //                {
        //                    strWhere += " or ";
        //                }
        //            }
        //        }

        //        strSQL = "SELECT exp.bgm_exp_seq, exp.bgm_exp_name, exp.bgm_exp_grp_seq"
        //            + ", expgrp.bgm_exp_grp_name, es.bgm_source_seq, s.bgm_source_name"
        //            + ", pjexp.amount, pjexp.currency_seq, pjexp.exchange_rate, pjexp.bath_amount"
        //            + ", recexp.bgm_proj_seq, cur.currency_name_short"
        //            + ", coalesce(recexp.total_amount, 0) as bgm_proj_exp_amount"
        //            + ", pjexp.bgm_proj_oper_exp_seq, pjexp.bgm_proj_oper_seq, recexp.bgm_proj_exp_seq"
        //            + ", pjexp.bgm_proj_oper_exp_descr, coalesce(rate.exp_rate_count, 0) as exp_rate_count"
                
        //            + " FROM bgm_mas_expense exp"
        //            + " LEFT JOIN bgm_mas_expense_group expgrp ON exp.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
        //            + " LEFT JOIN bgm_mas_expense_source es ON exp.bgm_exp_seq = es.bgm_exp_seq"
        //            + " LEFT JOIN bgm_mas_source s ON es.bgm_source_seq = s.bgm_source_seq"
        //            + " LEFT JOIN bgm_project_expense recexp ON exp.bgm_exp_seq = recexp.bgm_exp_seq"
        //                + " AND recexp.bgm_proj_seq = @bgm_proj_seq"
        //                + " AND es.bgm_source_seq = recexp.bgm_source_seq"
        //                //+ " AND recexp.record_status not like @delete_status"
        //            + " LEFT JOIN bgm_project_operation_expense pjexp ON exp.bgm_exp_seq = pjexp.bgm_exp_seq"
        //                + " AND es.bgm_source_seq = pjexp.bgm_source_seq"
        //                + " AND pjexp.bgm_proj_oper_seq = @bgm_proj_oper_seq"
        //                + " AND pjexp.record_status not like @delete_status"
        //            + " LEFT JOIN fim_mas_currencies cur ON pjexp.currency_seq = cur.currency_seq"
        //            + " LEFT JOIN (SELECT rate.bgm_exp_seq, rate.bgm_source_seq"
        //                + ", count(*) as exp_rate_count"
        //                + " FROM fim_mas_expense_rates rate"
        //                + " WHERE rate.record_status not like @delete_status"
        //                + " GROUP BY rate.bgm_exp_seq, rate.bgm_source_seq"
        //                + ") rate ON exp.bgm_exp_seq = rate.bgm_exp_seq"
        //                    + " AND es.bgm_source_seq = rate.bgm_source_seq"
        //            + " LEFT JOIN (SELECT exp.upper_bgm_exp_seq, count(*) as child_count"
        //                + " FROM bgm_mas_expense exp"
        //                + " WHERE exp.record_status not like @delete_status"
        //                + " GROUP BY exp.upper_bgm_exp_seq"
        //                + ") lexp ON exp.bgm_exp_seq = lexp.upper_bgm_exp_seq"
        //            + " WHERE exp.record_status not like @delete_status"
        //            + " AND lexp.child_count is null"
        //            + strWhere;

        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            for (int i = 0; i < drc.Count; i++)
        //            {
        //                M_BGM_ProjectOperationExpense info = new M_BGM_ProjectOperationExpense();
        //                info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_exp_seq"]);
        //                info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);
        //                info.bgm_proj_oper_exp_descr = drc[i]["bgm_proj_oper_exp_descr"].ToString();
        //                info.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);
                        
        //                info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
        //                info.currency_seq = cn.ConvertNullInt(drc[i]["currency_seq"]);
        //                info.exchange_rate = cn.ConvertNullDouble(drc[i]["exchange_rate"]);
        //                info.bath_amount = cn.ConvertNullDouble(drc[i]["bath_amount"]);
        //                info.exp_rate_count = cn.ConvertNullInt(drc[i]["exp_rate_count"]);

        //                M_BGM_ProjectExpense subInfo1 = new M_BGM_ProjectExpense();
        //                M_BGM_MAS_Expense subInfo2 = new M_BGM_MAS_Expense();
        //                M_BGM_MAS_ExpenseGroup subInfo3 = new M_BGM_MAS_ExpenseGroup();
        //                M_BGM_MAS_Source subInfo4 = new M_BGM_MAS_Source();
        //                M_FIM_MAS_Currencies subInfo5 = new M_FIM_MAS_Currencies();

        //                subInfo1.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
        //                subInfo1.bgm_proj_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_exp_seq"]);
                        
        //                subInfo1.amount = cn.ConvertNullDouble(drc[i]["bgm_proj_exp_amount"]);
        //                subInfo2.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
        //                subInfo2.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
        //                subInfo3.bgm_exp_grp_name = drc[i]["bgm_exp_grp_name"].ToString();
        //                subInfo4.bgm_source_name = drc[i]["bgm_source_name"].ToString();
        //                subInfo5.currency_name_short = drc[i]["currency_name_short"].ToString();

        //                subInfo1.m_bgm_mas_expense = subInfo2;
        //                subInfo1.m_bgm_mas_expensegroup = subInfo3;
        //                subInfo1.m_bgm_mas_source = subInfo4;
        //                info.m_bgm_projectexpense_temp = subInfo1;
        //                info.m_fim_mas_currencies = subInfo5;

        //                infoList.Add(info);
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return infoList;
        //}

        public IList<M_BGM_ProjectOperationExpense> GetProjectExpense(Nullable<int> bgm_proj_oper_seq
            , Nullable<int> bgm_proj_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationExpense> infoList = new List<M_BGM_ProjectOperationExpense>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@receive_report_type", SystemInfo._ReceiveReportType);
                dbManager.AddParameters(2, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(3, "@bgm_proj_seq", bgm_proj_seq);

                strSQL = "SELECT pjexp.bgm_exp_seq, exp.bgm_exp_name, exp.bgm_exp_grp_seq"
                    + ", expgrp.bgm_exp_grp_name, pjexp.bgm_source_seq, s.bgm_source_name"
                    + ", opexp.amount"
                    + ", pjexp.bgm_proj_seq, pjexp.descr"
                    + ", coalesce(rpt.cal_amount, 0) as bgm_proj_exp_amount"
                    + ", opexp.bgm_proj_oper_exp_seq, opexp.bgm_proj_oper_seq, pjexp.bgm_proj_exp_seq"
                    + ", opexp.bgm_proj_oper_exp_descr, coalesce(rate.exp_rate_count, 0) as exp_rate_count"

                    + ", pjexp.icm_res_item_seq"
                    + ", pjexp.unit_price as unit_price_proj, pjexp.unit_qty as unit_qty_proj"
                    + ", opexp.unit_price, opexp.unit_qty"

                    + ", exp.upper_bgm_exp_seq as bgm_exp_seq1, exp1.bgm_exp_name as bgm_exp_name1"
                    + ", exp1.upper_bgm_exp_seq as bgm_exp_seq2, exp2.bgm_exp_name as bgm_exp_name2"

                    + " FROM bgm_project_expense pjexp"
                    + " LEFT JOIN bgm_mas_expense exp ON pjexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp1 ON exp.upper_bgm_exp_seq = exp1.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp2 ON exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense_group expgrp ON exp.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
                    + " LEFT JOIN bgm_mas_source s ON pjexp.bgm_source_seq = s.bgm_source_seq"
                    //+ " LEFT JOIN (SELECT pjrpt.bgm_proj_exp_seq"
                    //    + ", sum(coalesce(pjrpt.cal_amount, 0)) AS cal_amount"
                    //    + " FROM bgm_project_report pjrpt"
                    //    //+ " LEFT JOIN bgm_project_operation_expense opexp ON pjrpt.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    //    + " WHERE pjrpt.record_status not like @delete_status"
                    //    + " AND pjrpt.rpt_type in (@receive_report_type)"
                    //    + " GROUP BY pjrpt.bgm_proj_exp_seq"
                    //    + ") rpt1 ON pjexp.bgm_proj_exp_seq = rpt1.bgm_proj_exp_seq"
                    + " LEFT JOIN (SELECT pjrpt.bgm_proj_exp_seq"
                        + ", sum(coalesce(pjrpt.cal_amount, 0)) AS cal_amount"
                        + " FROM bgm_project_report pjrpt"
                        + " LEFT JOIN bgm_project_operation_expense opexp ON pjrpt.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                        + " WHERE pjrpt.record_status not like @delete_status"
                        + (bgm_proj_oper_seq != null ? " AND coalesce(opexp.bgm_proj_oper_seq, -1) not like @bgm_proj_oper_seq" : "")
                        + " GROUP BY pjrpt.bgm_proj_exp_seq"
                        + ") rpt ON pjexp.bgm_proj_exp_seq = rpt.bgm_proj_exp_seq"
                    + " LEFT JOIN (SELECT rate.bgm_exp_seq, rate.bgm_source_seq"
                        + ", count(*) as exp_rate_count"
                        + " FROM fim_mas_expense_rates rate"
                        + " WHERE rate.record_status not like @delete_status"
                        + " GROUP BY rate.bgm_exp_seq, rate.bgm_source_seq"
                        + ") rate ON pjexp.bgm_exp_seq = rate.bgm_exp_seq"
                            + " AND pjexp.bgm_source_seq = rate.bgm_source_seq"
                    + " LEFT JOIN bgm_project_operation_expense opexp ON pjexp.bgm_proj_exp_seq = opexp.bgm_proj_exp_seq"
                        + (bgm_proj_oper_seq != null ? " AND opexp.bgm_proj_oper_seq = @bgm_proj_oper_seq"
                        : " AND opexp.bgm_proj_oper_seq = -1")
                    //+ " LEFT JOIN fim_mas_currencies cur ON opexp.currency_seq = cur.currency_seq"
                    
                    + " WHERE 0 = 0"//pjexp.record_status not like @delete_status"
                    + (bgm_proj_seq != null ? " AND pjexp.bgm_proj_seq = @bgm_proj_seq" : "")
                    + " ORDER BY s.bgm_source_seq, exp.bgm_exp_grp_seq, exp1.upper_bgm_exp_seq, exp.upper_bgm_exp_seq, exp.bgm_exp_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationExpense info = new M_BGM_ProjectOperationExpense();
                        info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_exp_seq"]);
                        info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);
                        info.bgm_proj_oper_exp_descr = drc[i]["bgm_proj_oper_exp_descr"].ToString();
                        info.bgm_proj_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_exp_seq"]);

                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.exp_rate_count = cn.ConvertNullInt(drc[i]["exp_rate_count"]);

                        info.unit_price = cn.ConvertNullDouble(drc[i]["unit_price"]);
                        info.unit_qty = cn.ConvertNullInt(drc[i]["unit_qty"]);

                        M_BGM_ProjectExpense subInfo1 = new M_BGM_ProjectExpense();
                        M_BGM_MAS_Expense subInfo2 = new M_BGM_MAS_Expense();
                        //M_BGM_MAS_ExpenseGroup subInfo3 = new M_BGM_MAS_ExpenseGroup();
                        M_BGM_MAS_Source subInfo4 = new M_BGM_MAS_Source();

                        M_BGM_MAS_Expense subInfo6 = new M_BGM_MAS_Expense();
                        M_BGM_MAS_Expense subInfo7 = new M_BGM_MAS_Expense();

                        subInfo1.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        subInfo1.bgm_proj_seq = cn.ConvertNullInt(drc[i]["bgm_proj_seq"]);
                        subInfo1.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);
                        subInfo1.icm_res_item_seq = cn.ConvertNullInt(drc[i]["icm_res_item_seq"]);
                        subInfo1.unit_price = cn.ConvertNullDouble(drc[i]["unit_price_proj"]);
                        subInfo1.unit_qty = cn.ConvertNullInt(drc[i]["unit_qty_proj"]);
                        subInfo1.descr = drc[i]["descr"].ToString();

                        subInfo1.amount = cn.ConvertNullDouble(drc[i]["bgm_proj_exp_amount"]);
                        subInfo2.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
                        subInfo2.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        subInfo2.bgm_exp_grp_name = drc[i]["bgm_exp_grp_name"].ToString();
                        subInfo2.upper_bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq1"]);
                        subInfo4.bgm_source_name = drc[i]["bgm_source_name"].ToString();

                        subInfo6.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq1"]);
                        subInfo6.bgm_exp_name = drc[i]["bgm_exp_name1"].ToString();
                        subInfo6.upper_bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq2"]);

                        subInfo7.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq2"]);
                        subInfo7.bgm_exp_name = drc[i]["bgm_exp_name2"].ToString();

                        subInfo6.m_bgm_mas_expense_upper = subInfo7;
                        subInfo2.m_bgm_mas_expense_upper = subInfo6;

                        subInfo1.m_bgm_mas_expense = subInfo2;
                        //subInfo1.m_bgm_mas_expensegroup = subInfo3;
                        subInfo1.m_bgm_mas_source = subInfo4;
                        info.m_bgm_projectexpense = subInfo1;

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperationExpense> GetProjectExpense(int bgm_proj_oper_seq
            , int bgm_proj_seq, int bgm_proj_oper_edit_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationExpense> infoList = new List<M_BGM_ProjectOperationExpense>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@receive_report_type", SystemInfo._ReceiveReportType);
                dbManager.AddParameters(2, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(3, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(4, "@bgm_proj_oper_edit_seq", bgm_proj_oper_edit_seq);

                strSQL = @"
SELECT pjexp.bgm_exp_seq, exp.bgm_exp_name, exp.bgm_exp_grp_seq
, expgrp.bgm_exp_grp_name, pjexp.bgm_source_seq, s.bgm_source_name
, opexp.amount
, pjexp.bgm_proj_seq, pjexp.descr
, coalesce(rpt.cal_amount, 0) as bgm_proj_exp_amount
, opexp.bgm_proj_oper_exp_seq, opexp.bgm_proj_oper_seq, pjexp.bgm_proj_exp_seq
, opexp.bgm_proj_oper_exp_descr

, pjexp.icm_res_item_seq
, pjexp.unit_price as unit_price_proj, pjexp.unit_qty as unit_qty_proj
, opexp.unit_price, opexp.unit_qty

, exp.upper_bgm_exp_seq as bgm_exp_seq1, exp1.bgm_exp_name as bgm_exp_name1
, exp1.upper_bgm_exp_seq as bgm_exp_seq2, exp2.bgm_exp_name as bgm_exp_name2

, ede.bgm_proj_oper_edit_seq, ede.bgm_proj_oper_edit_exp_seq
, ede.old_amount, ede.add_amount, ede.old_amount + ede.add_amount as total_amount

FROM bgm_project_expense pjexp
LEFT JOIN bgm_mas_expense exp ON pjexp.bgm_exp_seq = exp.bgm_exp_seq
LEFT JOIN bgm_mas_expense exp1 ON exp.upper_bgm_exp_seq = exp1.bgm_exp_seq
LEFT JOIN bgm_mas_expense exp2 ON exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq
LEFT JOIN bgm_mas_expense_group expgrp ON exp.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq
LEFT JOIN bgm_mas_source s ON pjexp.bgm_source_seq = s.bgm_source_seq
LEFT JOIN (SELECT pjrpt.bgm_proj_exp_seq
		, sum(coalesce(pjrpt.cal_amount, 0)) AS cal_amount
		FROM bgm_project_report pjrpt
		WHERE pjrpt.record_status not like 'D'
		GROUP BY pjrpt.bgm_proj_exp_seq
		) rpt ON pjexp.bgm_proj_exp_seq = rpt.bgm_proj_exp_seq
LEFT JOIN bgm_project_operation_expense opexp ON pjexp.bgm_proj_exp_seq = opexp.bgm_proj_exp_seq
		AND opexp.bgm_proj_oper_seq = @bgm_proj_oper_seq
LEFT JOIN bgm_project_operation_edit_expense ede ON (opexp.bgm_proj_oper_exp_seq = ede.bgm_proj_oper_exp_seq or pjexp.bgm_proj_exp_seq = ede.bgm_proj_exp_seq)
		AND ede.bgm_proj_oper_edit_seq = @bgm_proj_oper_edit_seq

WHERE pjexp.bgm_proj_seq = @bgm_proj_seq
ORDER BY s.bgm_source_seq, exp.bgm_exp_grp_seq, exp1.upper_bgm_exp_seq, exp.upper_bgm_exp_seq, exp.bgm_exp_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationExpense info = new M_BGM_ProjectOperationExpense();
                        info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_exp_seq"]);
                        info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);
                        info.bgm_proj_oper_exp_descr = drc[i]["bgm_proj_oper_exp_descr"].ToString();
                        info.bgm_proj_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_exp_seq"]);

                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        //info.exp_rate_count = cn.ConvertNullInt(drc[i]["exp_rate_count"]);

                        info.unit_price = cn.ConvertNullDouble(drc[i]["unit_price"]);
                        info.unit_qty = cn.ConvertNullInt(drc[i]["unit_qty"]);

                        info.bgm_proj_oper_edit_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_edit_seq"]);
                        info.bgm_proj_oper_edit_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_edit_exp_seq"]);
                        info.old_amount = cn.ConvertNullDouble(drc[i]["old_amount"]) ?? info.amount;
                        info.add_amount = cn.ConvertNullDouble(drc[i]["add_amount"]);
                        info.total_amount = cn.ConvertNullDouble(drc[i]["total_amount"]);

                        M_BGM_ProjectExpense subInfo1 = new M_BGM_ProjectExpense();
                        M_BGM_MAS_Expense subInfo2 = new M_BGM_MAS_Expense();
                        //M_BGM_MAS_ExpenseGroup subInfo3 = new M_BGM_MAS_ExpenseGroup();
                        M_BGM_MAS_Source subInfo4 = new M_BGM_MAS_Source();

                        M_BGM_MAS_Expense subInfo6 = new M_BGM_MAS_Expense();
                        M_BGM_MAS_Expense subInfo7 = new M_BGM_MAS_Expense();

                        subInfo1.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        subInfo1.bgm_proj_seq = cn.ConvertNullInt(drc[i]["bgm_proj_seq"]);
                        subInfo1.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);
                        subInfo1.icm_res_item_seq = cn.ConvertNullInt(drc[i]["icm_res_item_seq"]);
                        subInfo1.unit_price = cn.ConvertNullDouble(drc[i]["unit_price_proj"]);
                        subInfo1.unit_qty = cn.ConvertNullInt(drc[i]["unit_qty_proj"]);
                        subInfo1.descr = drc[i]["descr"].ToString();

                        subInfo1.amount = cn.ConvertNullDouble(drc[i]["bgm_proj_exp_amount"]);
                        subInfo2.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
                        subInfo2.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        subInfo2.bgm_exp_grp_name = drc[i]["bgm_exp_grp_name"].ToString();
                        subInfo2.upper_bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq1"]);
                        subInfo4.bgm_source_name = drc[i]["bgm_source_name"].ToString();

                        subInfo6.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq1"]);
                        subInfo6.bgm_exp_name = drc[i]["bgm_exp_name1"].ToString();
                        subInfo6.upper_bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq2"]);

                        subInfo7.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq2"]);
                        subInfo7.bgm_exp_name = drc[i]["bgm_exp_name2"].ToString();

                        subInfo6.m_bgm_mas_expense_upper = subInfo7;
                        subInfo2.m_bgm_mas_expense_upper = subInfo6;

                        subInfo1.m_bgm_mas_expense = subInfo2;
                        //subInfo1.m_bgm_mas_expensegroup = subInfo3;
                        subInfo1.m_bgm_mas_source = subInfo4;
                        info.m_bgm_projectexpense = subInfo1;

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperationExpense> GetList(Nullable<int> bgm_proj_oper_seq
            , Nullable<int> bgm_source_seq, Nullable<int> bgm_exp_grp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationExpense> infoList = new List<M_BGM_ProjectOperationExpense>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                strSQL = "SELECT pjexp.bgm_proj_oper_exp_seq, pjexp.bgm_proj_oper_exp_descr"
                    + ", pjexp.amount"
                    + ", recexp.bgm_exp_seq, exp.bgm_exp_name"
                    + ", recexp.bgm_source_seq, s.bgm_source_name"
                    + ", pjexp.bgm_proj_oper_seq"
                    + " FROM bgm_project_operation_expense pjexp"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_source s ON recexp.bgm_source_seq = s.bgm_source_seq"
                    + " WHERE pjexp.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " AND pjexp.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + (bgm_source_seq != null ? " AND recexp.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_exp_grp_seq != null ? " AND exp.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationExpense info = new M_BGM_ProjectOperationExpense();
                        info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_exp_seq"]);
                        info.bgm_proj_oper_exp_descr = drc[i]["bgm_proj_oper_exp_descr"].ToString();
                        
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);

                        M_BGM_ProjectExpense subInfo1 = new M_BGM_ProjectExpense();
                        M_BGM_MAS_Expense subInfo2 = new M_BGM_MAS_Expense();
                        M_BGM_MAS_Source subInfo3 = new M_BGM_MAS_Source();
                        subInfo1.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);
                        subInfo1.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        subInfo2.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
                        subInfo3.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        subInfo1.m_bgm_mas_expense = subInfo2;
                        subInfo1.m_bgm_mas_source = subInfo3;
                        info.m_bgm_projectexpense = subInfo1;

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperationExpense> GetGroupBgmSource(Nullable<int> bgm_proj_oper_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationExpense> infoList = new List<M_BGM_ProjectOperationExpense>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);

                strSQL = "SELECT pjexp.bgm_proj_oper_seq, recexp.bgm_source_seq, s.bgm_source_name"
                    + ", sum(coalesce(pjexp.amount, 0)) as amount"
                    + " FROM bgm_project_operation_expense pjexp"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_source s ON recexp.bgm_source_seq = s.bgm_source_seq"
                    + " WHERE pjexp.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " AND pjexp.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + " GROUP BY pjexp.bgm_proj_oper_seq, recexp.bgm_source_seq, s.bgm_source_name";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationExpense info = new M_BGM_ProjectOperationExpense();
                        info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);

                        M_BGM_ProjectExpense info1 = new M_BGM_ProjectExpense();
                        M_BGM_MAS_Source info2 = new M_BGM_MAS_Source();
                        info1.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info2.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info1.m_bgm_mas_source = info2;
                        info.m_bgm_projectexpense = info1;

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperationExpense> GetGroupBgmExpGrp(Nullable<int> bgm_proj_oper_seq
            , Nullable<int> bgm_source_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationExpense> infoList = new List<M_BGM_ProjectOperationExpense>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);

                strSQL = "SELECT pjexp.bgm_proj_oper_seq, exp.bgm_exp_grp_seq, expgrp.bgm_exp_grp_name"
                    + ", sum(coalesce(pjexp.amount, 0)) as amount"
                    + " FROM bgm_project_operation_expense pjexp"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense_group expgrp ON exp.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
                    + " WHERE pjexp.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " AND pjexp.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + (bgm_source_seq != null ? " AND recexp.bgm_source_seq = @bgm_source_seq" : "")
                    + " GROUP BY pjexp.bgm_proj_oper_seq, exp.bgm_exp_grp_seq, expgrp.bgm_exp_grp_name";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationExpense info = new M_BGM_ProjectOperationExpense();
                        info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);

                        M_BGM_ProjectExpense info1 = new M_BGM_ProjectExpense();
                        M_BGM_MAS_Expense info2 = new M_BGM_MAS_Expense();
                        info2.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        info2.bgm_exp_grp_name = drc[i]["bgm_exp_grp_name"].ToString();
                        info1.m_bgm_mas_expense = info2;
                        info.m_bgm_projectexpense = info1;

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperationExpense> GetGroupBgmExp(Nullable<int> bgm_proj_oper_seq
            , Nullable<int> bgm_source_seq, Nullable<int> bgm_exp_grp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperationExpense> infoList = new List<M_BGM_ProjectOperationExpense>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                strSQL = "SELECT pjexp.bgm_proj_oper_seq, recexp.bgm_exp_seq, exp.bgm_exp_name"
                    + ", sum(coalesce(pjexp.amount, 0)) as amount"
                    + " FROM bgm_project_operation_expense pjexp"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " WHERE pjexp.record_status not like @delete_status"
                    + (bgm_proj_oper_seq != null ? " AND pjexp.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + (bgm_source_seq != null ? " AND recexp.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_exp_grp_seq != null ? " AND exp.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "")
                    + " GROUP BY pjexp.bgm_proj_oper_seq, recexp.bgm_exp_seq, exp.bgm_exp_name";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperationExpense info = new M_BGM_ProjectOperationExpense();
                        info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);

                        M_BGM_ProjectExpense info1 = new M_BGM_ProjectExpense();
                        M_BGM_MAS_Expense info2 = new M_BGM_MAS_Expense();
                        info1.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);
                        info2.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
                        info1.m_bgm_mas_expense = info2;
                        info.m_bgm_projectexpense = info1;

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_BGM_ProjectOperationExpense GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_BGM_ProjectOperationExpense info = new M_BGM_ProjectOperationExpense();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT pjexp.bgm_proj_oper_exp_seq, pjexp.bgm_proj_oper_seq"
                    + ", pjexp.bgm_proj_oper_exp_descr, recexp.bgm_source_seq, pjexp.bgm_proj_exp_seq"
                    + ", pjexp.amount"
                    + ", source.bgm_source_name"
                    + ", pjexp.unit_price, pjexp.unit_qty"

                    + ", recexp.bgm_exp_seq, exp.bgm_exp_name"
                    + ", exp.bgm_exp_grp_seq, expgrp.bgm_exp_grp_name"
                    + ", pjexp.record_status"
                    + " FROM bgm_project_operation_expense pjexp"
                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_source source ON recexp.bgm_source_seq = source.bgm_source_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense_group expgrp ON exp.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
                    + " WHERE pjexp.bgm_proj_oper_exp_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[0]["bgm_proj_oper_exp_seq"]);
                    info.bgm_proj_oper_seq = cn.ConvertNullInt(drc[0]["bgm_proj_oper_seq"]);
                    info.bgm_proj_oper_exp_descr = drc[0]["bgm_proj_oper_exp_descr"].ToString();
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.unit_price = cn.ConvertNullDouble(drc[0]["unit_price"]);
                    info.unit_qty = cn.ConvertNullInt(drc[0]["unit_qty"]);

                    M_BGM_ProjectExpense subInfo1 = new M_BGM_ProjectExpense();
                    M_BGM_MAS_Expense subInfo2 = new M_BGM_MAS_Expense();
                    //M_BGM_MAS_ExpenseGroup subInfo3 = new M_BGM_MAS_ExpenseGroup();
                    M_BGM_MAS_Source subInfo4 = new M_BGM_MAS_Source();

                    subInfo1.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    subInfo1.bgm_exp_seq = cn.ConvertNullInt(drc[0]["bgm_exp_seq"]);
                    subInfo2.bgm_exp_name = drc[0]["bgm_exp_name"].ToString();
                    subInfo2.bgm_exp_grp_seq = cn.ConvertNullInt(drc[0]["bgm_exp_grp_seq"]);
                    subInfo2.bgm_exp_grp_name = drc[0]["bgm_exp_grp_name"].ToString();
                    subInfo4.bgm_source_name = drc[0]["bgm_source_name"].ToString();

                    subInfo1.m_bgm_mas_expense = subInfo2;
                    subInfo1.m_bgm_mas_source = subInfo4;
                    info.m_bgm_projectexpense = subInfo1;

                    info.record_status = drc[0]["record_status"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public bool CheckAmount(int bgm_proj_oper_exp_seq, double amount)
        {
            try
            {
                DataTable dtb = null;

                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@bgm_proj_oper_exp_seq", bgm_proj_oper_exp_seq);
                string strSQL = @"
SELECT sum(coalesce(rpt.cal_amount, 0)) as amount
from bgm_project_operation_expense ope
left join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
left join bgm_project_operation_expense ope1 on ope.bgm_proj_oper_seq = ope1.bgm_proj_oper_seq
left join bgm_project_expense pje1 on ope1.bgm_proj_exp_seq = pje1.bgm_proj_exp_seq
	and pje1.bgm_source_seq = pje.bgm_source_seq
left join bgm_project_operation_report rpt on ope1.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq

where ope.bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq
and ope.record_status <> 'D'
and rpt.record_status <> 'D'
and pje1.bgm_proj_exp_seq is not null
";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0 && amount > (cn.ConvertNullDouble(dtb.Rows[0][0]) ?? 0))
                {
                    return false;
                }

                return true;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public double GetRemainProject(string bgm_proj_oper_exp_seq)
        {
            DataTable dtb = null;

            dbManager.Open();
            dbManager.CreateParameters(1);

            dbManager.AddParameters(0, "@bgm_proj_oper_exp_seq", bgm_proj_oper_exp_seq);
            string strSQL = @"
select bgm_proj_oper_seq, bgm_source_seq, bgm_exp_grp_seq
from bgm_project_operation_expense oe
inner join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
inner join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
where oe.bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq
";
            dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            if (dtb.Rows.Count > 0)
            {
                string bgm_proj_oper_seq = "";
                string bgm_source_seq = "";
                string bgm_exp_grp_seq = "";
                return GetRemainProject(bgm_proj_oper_seq, bgm_source_seq, bgm_exp_grp_seq);
            }
            else
            {
                return 0;
            }
        }

        public double GetRemainProject(string bgm_proj_oper_seq, string bgm_source_seq, string bgm_exp_grp_seq)
        {
            try
            {
                DataTable dtb = null;

                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_exp_grp_seq", bgm_exp_grp_seq);
                string strSQL = @"
select coalesce(oe.amount, 0)
	- (coalesce(po.amount, 0) - coalesce(mrpo.amount, 0))
	- coalesce(mr.amount, 0) + coalesce(r.amount, 0) as remain_amount
from bgm_project_operation o
left join (select oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
	, sum(coalesce(rpt.cal_amount ,0)) as amount
	from bgm_project_operation_expense oe
	left join bgm_project_operation_report rpt on oe.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq
	left join bgm_project_expense pe on oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
	left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
	where oe.record_status not like 'D'
	and rpt.record_status not like 'D'
	and rpt.rpt_type = 1
	group by oe.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
	) oe on o.bgm_proj_oper_seq = oe.bgm_proj_oper_seq

left join (select ope.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
	, sum((COALESCE(quo.NetAmount, 0) / COALESCE(quo.Qty, 1)) * ppri.Qty) as amount
	from IcmPurchaseRequestItem pri
  inner join IcmPurchaseRequest pr on pri.PrId = pr.PrId
	inner join IcmBidderQuotation quo on pri.PrItemId = quo.PrItemId
	inner join IcmPurchaseOrder po on quo.BidderId = po.BidderId and po.RecordStatus = 'A'
	inner join (select ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId, sum(ppri.Qty) as Qty
		from IcmPrePurchaseRequestItem ppri
		group by ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId
	) ppri on pri.PrItemId = ppri.PrItemId
	inner join bgm_project_operation_expense ope on ppri.ProjOperExpId = ope.bgm_proj_oper_exp_seq
	left join bgm_project_expense pe on ope.bgm_proj_exp_seq = pe.bgm_proj_exp_seq
	left join bgm_mas_expense e on pe.bgm_exp_seq = e.bgm_exp_seq
	where po.RecordStatus = 'A'
	and po.Closed = 0
	and pri.RecordStatus <> 'D'
	group by ope.bgm_proj_oper_seq, pe.bgm_source_seq, e.bgm_exp_grp_seq
) po on o.bgm_proj_oper_seq = po.bgm_proj_oper_seq
	and oe.bgm_source_seq = po.bgm_source_seq
	and oe.bgm_exp_grp_seq = po.bgm_exp_grp_seq

left join (select mr.bgm_proj_oper_seq, mr.bgm_source_seq, mr.bgm_exp_grp_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	inner join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId
	inner join IcmPurchaseOrder po on dn.PoId = po.PoId
	where mr.record_status = 'A'
	and mr.event_seq = 11
	and po.Closed = 0
	and mrd.record_status <> 'D'
	group by mr.bgm_proj_oper_seq, mr.bgm_source_seq, mr.bgm_exp_grp_seq
) mrpo on o.bgm_proj_oper_seq = mrpo.bgm_proj_oper_seq
	and oe.bgm_source_seq = mrpo.bgm_source_seq
	and oe.bgm_exp_grp_seq = mrpo.bgm_exp_grp_seq

left join (select mr.bgm_proj_oper_seq, mr.bgm_source_seq, mr.bgm_exp_grp_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	where mr.record_status = 'A'
	and mrd.record_status <> 'D'
	group by mr.bgm_proj_oper_seq, mr.bgm_source_seq, mr.bgm_exp_grp_seq
) mr on o.bgm_proj_oper_seq = mr.bgm_proj_oper_seq
	and oe.bgm_source_seq = mr.bgm_source_seq
	and oe.bgm_exp_grp_seq = mr.bgm_exp_grp_seq

left join (select mr.bgm_proj_oper_seq, p.bgm_source_seq, mr.bgm_exp_grp_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_payments p
	inner join fim_money_request_dtls mrd on p.money_req_seq = mrd.money_req_seq
	inner join fim_money_requests mr on p.money_req_seq = mr.money_req_seq
	where p.record_status = 'A'
	and mrd.record_status <> 'D'
	group by mr.bgm_proj_oper_seq, p.bgm_source_seq, mr.bgm_exp_grp_seq
) p on o.bgm_proj_oper_seq = p.bgm_proj_oper_seq
	and oe.bgm_source_seq = p.bgm_source_seq
	and oe.bgm_exp_grp_seq = p.bgm_exp_grp_seq

left join (select mr.bgm_proj_oper_seq, r.bgm_source_seq, mr.bgm_exp_grp_seq
	, sum(coalesce(r.amount, 0)) as amount
	from fim_receives r
	inner join fim_money_requests mr on r.money_req_seq = mr.money_req_seq
	where r.record_status not in ('D', 'C')
	group by mr.bgm_proj_oper_seq, r.bgm_source_seq, mr.bgm_exp_grp_seq
) r on o.bgm_proj_oper_seq = r.bgm_proj_oper_seq
	and oe.bgm_source_seq = r.bgm_source_seq
	and oe.bgm_exp_grp_seq = r.bgm_exp_grp_seq

where 0 = 0"
+ (bgm_proj_oper_seq != null ? " AND oe.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
+ (bgm_source_seq != null ? " AND oe.bgm_source_seq = @bgm_source_seq" : "")
+ (bgm_exp_grp_seq != null ? " AND oe.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "")
;
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    return cn.ConvertNullDouble(dtb.Rows[0][0]) ?? 0;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string Insert(M_BGM_ProjectOperationExpense info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@bgm_proj_oper_exp_seq", info.bgm_proj_oper_exp_seq);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", info.bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_descr", info.bgm_proj_oper_exp_descr);
                dbManager.AddParameters(3, "@bgm_proj_exp_seq", info.bgm_proj_exp_seq);
                dbManager.AddParameters(4, "@amount", info.amount);
                dbManager.AddParameters(5, "@currency_seq", "");
                dbManager.AddParameters(6, "@exchange_rate", "");
                dbManager.AddParameters(7, "@bath_amount", "");
                dbManager.AddParameters(8, "@unit_price", info.unit_price);
                dbManager.AddParameters(9, "@unit_qty", info.unit_qty);

                dbManager.AddParameters(10, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(11, "@created_by", info.created_by);
                dbManager.AddParameters(12, "@created_when", info.created_when);

                strSQL = "INSERT INTO bgm_project_operation_expense"
                    + "(bgm_proj_oper_seq, bgm_proj_oper_exp_descr, bgm_proj_exp_seq"
                    + ", amount"
                    + ", unit_price, unit_qty"
                    + ", record_status, created_by, created_when)"
                    + " VALUES (@bgm_proj_oper_seq"
                        + ", @bgm_proj_oper_exp_descr"
                        + ", @bgm_proj_exp_seq"
                        + ", @amount"
                        //+ ", @currency_seq"
                        //+ ", @exchange_rate"
                        //+ ", @bath_amount"
                        + ", @unit_price"
                        + ", @unit_qty"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + ");"
                    + " SELECT scope_identity();";
                var result = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                return result;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_BGM_ProjectOperationExpense info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@bgm_proj_oper_exp_seq", info.bgm_proj_oper_exp_seq);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", info.bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@bgm_proj_oper_exp_descr", info.bgm_proj_oper_exp_descr);
                dbManager.AddParameters(3, "@bgm_proj_exp_seq", info.bgm_proj_exp_seq);
                dbManager.AddParameters(4, "@amount", info.amount);
                dbManager.AddParameters(5, "@currency_seq", "");
                dbManager.AddParameters(6, "@exchange_rate", "");
                dbManager.AddParameters(7, "@bath_amount", "");
                dbManager.AddParameters(8, "@unit_price", info.unit_price);
                dbManager.AddParameters(9, "@unit_qty", info.unit_qty);

                dbManager.AddParameters(10, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(11, "@updated_by", info.updated_by);
                dbManager.AddParameters(12, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation_expense"
                    + " SET bgm_proj_oper_seq = @bgm_proj_oper_seq"
                    + ", bgm_proj_oper_exp_descr = @bgm_proj_oper_exp_descr"
                    + ", bgm_proj_exp_seq = @bgm_proj_exp_seq"
                    + ", amount = @amount"
                    //+ ", currency_seq = @currency_seq"
                    //+ ", exchange_rate = @exchange_rate"
                    //+ ", bath_amount = @bath_amount"
                    + ", unit_price = @unit_price"
                    + ", unit_qty = @unit_qty"
                    
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_BGM_ProjectOperationExpense info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@bgm_proj_oper_exp_seq", info.bgm_proj_oper_exp_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation_expense"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}
    }
}
